package com.sh.data.engine.domain.util;

import cn.hutool.core.collection.CollUtil;
import com.google.common.collect.Lists;
import com.sh.data.engine.common.exception.BusinessException;
import com.sh.data.engine.common.util.DateUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Optional;

/**
 * @author : fuchen
 * @date : 2022/1/4 16:11
 */
@Slf4j
public class JDBCUtil {

    public static String getJDBCDriver(String dsType) {
        if (StringUtils.isEmpty(dsType)) {
            return null;
        }
        String jdbcDriver = null;
        switch (dsType.toUpperCase()) {
            case "POSTGRESQL":
            case "MATRIXDB":
                jdbcDriver = "org.postgresql.Driver";
                break;
            case "MYSQL":
            case "TIDB":
            case "ESSQL":
                jdbcDriver = "com.mysql.cj.jdbc.Driver";
                break;
            case "ORACLE":
                jdbcDriver = "oracle.jdbc.driver.OracleDriver";
                break;
            case "DB2":
                jdbcDriver = "com.ibm.db2.jcc.DB2Driver";
                break;
            case "SQLSERVER":
                jdbcDriver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
                break;
            case "HIVE":
            case "CARBONDATA":
                jdbcDriver = "org.apache.hive.jdbc.HiveDriver";
                break;
            case "SAP":
                jdbcDriver = "com.sap.db.jdbc.Driver";
                break;
            case "GBASE":
                jdbcDriver = "com.gbase.jdbc.Driver";
                break;
            case "KYLIN":
                jdbcDriver = "org.apache.kylin.jdbc.Driver";
                break;
            case "LIBRA":
                jdbcDriver = "com.huawei.gauss200.jdbc.Driver";
                break;
            case "DM":
                jdbcDriver = "dm.jdbc.driver.DmDriver";
                break;
            case "KINGBASE8":
                jdbcDriver = "com.kingbase8.Driver";
                break;
            case "CLICKHOUSE":
                jdbcDriver = "ru.yandex.clickhouse.ClickHouseDriver";
                break;
            case "IOTDB":
                jdbcDriver = "org.apache.iotdb.jdbc.IoTDBDriver";
                break;
            case "AS400":
                jdbcDriver = "com.ibm.as400.access.AS400JDBCDriver";
                break;
        }
        return jdbcDriver;
    }

    public static String getRdbmsVersion(
        String dsType, String dsLink, String username, String password) throws SQLException {
        String version = null;

        try {
            Class.forName(getJDBCDriver(dsType));
        } catch (ClassNotFoundException e) {
            log.error(e.getMessage());
        }

        String queryVersionSql = getQueryVersionSql(dsType);

        if (StringUtils.isEmpty(queryVersionSql)) {
            return null;
        }

        int loginTimeout = DriverManager.getLoginTimeout();
        DriverManager.setLoginTimeout(10); // 设置4s后超时
        try (Connection connection = DriverManager.getConnection(dsLink, username, password);
             PreparedStatement pstmt = connection.prepareStatement(queryVersionSql);
             ResultSet resultSet = pstmt.executeQuery()) {
            if (resultSet.next()) {
                version = resultSet.getString(1);
            }
        } finally {
            DriverManager.setLoginTimeout(loginTimeout); // 再设置成默认值，以防对其他地方有影响
        }
        return version;
    }

    private static String getQueryVersionSql(String dsType) {
        String queryVersionSql = null;
        switch (dsType) {
            case "PostgreSQL":
            case "MatrixDB":
            case "LibrA":
                queryVersionSql = "SHOW server_version";
                break;
            case "MySQL":
            case "GBase":
            case "KINGBASE8":
                queryVersionSql = "SELECT VERSION()";
                break;
            case "Oracle":
            case "DM":
                queryVersionSql = "SELECT * FROM v$version";
                break;
            case "DB2":
                queryVersionSql = "SELECT service_level FROM TABLE (sysproc.env_get_inst_info())";
                break;
            case "SQLServer":
                queryVersionSql = "SELECT CONVERT(varchar(100), SERVERPROPERTY('productversion'))";
                break;
        }
        return queryVersionSql;
    }

    /**
     * 执行查询
     *
     * @param dsType
     * @param dsLink
     * @param sql
     * @param username
     * @param password
     * @param keyTabPath
     * @param krb5Conf
     * @return
     */
    public static String getWhere(
        String dsType,
        String dsLink,
        String sql,
        String username,
        String password,
        String keyTabPath,
        String krb5Conf)
        throws SQLException {
        String where = null;
        try {
            Class.forName(getJDBCDriver(dsType));
        } catch (ClassNotFoundException e) {
            log.error(e.getMessage());
        }

        if (!StringUtils.isEmpty(keyTabPath)) {
            try {
                Connection connection = null;
                // TODO 需要修改
        /*Connection connection =
        HiveUtil.getJdbcConnection(
                String.format("%s;principal=hive/node2@HADOOP.COM", dsLink),
                String.format("%s/node1@HADOOP.COM", username),
                null,
                krb5Conf,
                keyTabPath);*/

                PreparedStatement pstmt = connection.prepareStatement(sql);
                ResultSet resultSet = pstmt.executeQuery();

                int columnType = resultSet.getMetaData().getColumnType(1);
                String columnTypeName = resultSet.getMetaData().getColumnTypeName(1);
                int type = getValueType(columnType, columnTypeName);

                List<String> wheres = Lists.newArrayList();
                while (resultSet.next()) {

                    if (type == 1) {
                        wheres.add(resultSet.getString(1));
                    } else if (type == 2) {
                        Time time = resultSet.getTime(1);
                        String timeString = DateUtil.formatDate(time.getTime(), "HH:mm:ss");
                        wheres.add(String.format("'%s'", timeString));
                    } else if (type == 3) {
                        Date date = resultSet.getDate(1);
                        String dateString = DateUtil.formatDate(date.getTime(), "yyyy-MM-dd");
                        wheres.add(String.format("'%s'", dateString));
                    } else if (type == 4) {
                        Timestamp timestamp = resultSet.getTimestamp(1);
                        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        String timestampStr = df.format(timestamp);
                        wheres.add(String.format("'%s'", timestampStr));
                    } else {
                        wheres.add(String.format("'%s'", resultSet.getString(1)));
                    }
                }
                if (CollectionUtils.isNotEmpty(wheres)) {
                    where = StringUtils.join(wheres, ",");
                }
                resultSet.close();
                pstmt.close();
                connection.close();

            } catch (Exception e) {
                throw new BusinessException("获取连接失败");
            }

        } else {
            try (Connection connection = DriverManager.getConnection(dsLink, username, password);
                 PreparedStatement pstmt = connection.prepareStatement(sql);
                 ResultSet resultSet = pstmt.executeQuery()) {
                int columnType = resultSet.getMetaData().getColumnType(1);
                String columnTypeName = resultSet.getMetaData().getColumnTypeName(1);
                int type = getValueType(columnType, columnTypeName);

                List<String> wheres = Lists.newArrayList();
                while (resultSet.next()) {

                    // 无数据直接抛出异常
                    if (!Optional.ofNullable(resultSet.getObject(1)).isPresent()) {
                        throw new BusinessException(String.format("执行sql:{%s}查询的结果为空!", sql));
                    }
                    // 根据字段类型转换为string
                    switch (type) {
                        case 2:
                            Time time = resultSet.getTime(1);
                            String timeString = DateUtil.formatDate(time.getTime(), "HH:mm:ss");
                            wheres.add(String.format("'%s'", timeString));
                            break;
                        case 3:
                            Date date = resultSet.getDate(1);
                            String dateString = DateUtil.formatDate(date.getTime(), "yyyy-MM-dd");
                            wheres.add(String.format("'%s'", dateString));
                            break;
                        case 4:
                            Timestamp timestamp = resultSet.getTimestamp(1);
                            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                            String timestampStr = df.format(timestamp);
                            wheres.add(String.format("'%s'", timestampStr));
                            break;
                        case 1:
                        default: {
                            String value = resultSet.getString(1);
                            wheres.add(value);
                            break;
                        }
                    }
                }
                if (CollUtil.isEmpty(wheres)) {
                    throw new BusinessException("sql语句执行异常!");
                }
                where = StringUtils.join(wheres, ",");
            }
        }

        return where;
    }

    /**
     * 1-非字符串 2-time 3-date 4-TIMESTAMP,-1-字符串
     *
     * @param columnType
     * @param columnTypeName
     * @return
     */
    private static int getValueType(int columnType, String columnTypeName) {
        switch (columnType) {
            case Types.SMALLINT:
            case Types.TINYINT:
            case Types.INTEGER:
            case Types.BIGINT:
            case Types.NUMERIC:
            case Types.DECIMAL:
            case Types.FLOAT:
            case Types.REAL:
            case Types.DOUBLE:
                return 1;
            case Types.TIME:
                return 2;
            // for mysql bug, see http://bugs.mysql.com/bug.php?id=35115
            case Types.DATE:
                if ("year".equalsIgnoreCase(columnTypeName)) {
                    return 1;
                } else {
                    return 3;
                }
            case Types.TIMESTAMP:
                return 4;
            case Types.BINARY:
            case Types.VARBINARY:
            case Types.BLOB:
            case Types.LONGVARBINARY:
                return -1;
            case Types.BOOLEAN:
            case Types.BIT:
                return 1;
            case Types.NULL:
                return 1;
            default:
                return -1;
        }
    }
}
